import pandas as pd
from tqdm import tqdm

progress_bar = tqdm(total=100, desc=f"正在执行")

# 加载数据
shuju = pd.read_excel('excel_hwn/全店数据订单.xlsx', dtype={'商品原价销售额': int, '订单编号': str, '商品SKU码': str})
# 转换数据类型
shuju = shuju[['订单编号', '优惠活动', '商品SKU码', 'UPC码', '商品原价销售额', '商品名称']]

progress_bar.update(30)

# 加载数据2
mendian = pd.read_excel('excel_hwn/全部门店.xlsx', dtype={'订单号': str}, sheet_name=1)
bucanjia = pd.read_excel('excel_hwn/不参加.xlsx', dtype={'商品编码': str})
# 加载优惠卷明细
quan = pd.read_excel('excel_hwn/费用承担.xlsx')

progress_bar.update(30)

# 合并两个数据表
shuju['订单编号'] = shuju['订单编号'].str.strip()
mendian['订单号'] = mendian['订单号'].str.strip()

hebing = pd.merge(shuju, mendian, left_on='订单编号', right_on='订单号', how='left').drop_duplicates()

# 过滤是外卖订单的行
hebing = hebing[hebing['交易类型'] == "外卖订单"]

# 把券中所有的活动都添加到合并表中
activity_columns = quan['活动'].drop_duplicates().str.strip().tolist()

for column in activity_columns:
    hebing[column] = 0


# 判断类型函数
def panduanLeixing(row):
    if len(str(row)) < 4:
        return 5
    return 1 if str(row)[0:2] == '美团' else 0


# 全部数据过滤
hebing['代理类型'] = hebing['配送方式'].apply(panduanLeixing)


# 核心对比函数
def customOperation(row):
    activities = str(row['优惠活动']).split('/')

    gongyingshang = 0
    gongsichengdan = 0

    for active in activities:
        matches = quan[(quan['活动'] == active.strip()) & ((quan['代理'] == row['代理类型']) | (quan['代理'] == 2))]
        if not matches.empty:
            hebing.at[row.name, active.strip()] = -matches['供应商承担'].sum()
            gongyingshang += matches['供应商承担'].sum()
            gongsichengdan += matches['公司承担费用'].sum()

    hebing.at[row.name, '供应商承担费用'] = -gongyingshang
    hebing.at[row.name, '公司承担费用'] = -gongsichengdan
    hebing.at[row.name, '门店承担费用'] = -(-row['商家活动支出'] - gongyingshang - gongsichengdan)
    return row


# 所有数据经过自定义计算公式
hebing.apply(customOperation, axis=1)
hebing.to_excel("x.xlsx",index=False)
exit(0)
# 找出不参加的
hebing['商品SKU码'] = hebing['商品SKU码'].str.strip()
bucanjia['商品编码'] = bucanjia['商品编码'].str.strip()
hebing = pd.merge(left=hebing, right=bucanjia, left_on='商品SKU码', right_on='商品编码', how='left',
                  indicator='bcj').drop_duplicates()
# 只保留参加的

hebing = hebing[hebing['bcj'] != "both"]

# 获取订单总金额
hebing['订单总金额'] = hebing.groupby('订单编号')['商品原价销售额'].transform('sum')

# 使用广播运算进行计算
for column in activity_columns:
    # print(column, end='\n')
    hebing[column] = hebing['商品原价销售额'] / hebing['订单总金额'] * hebing[column]

progress_bar.update(30)

# 生成新excel
hebing.to_excel("excel_hwn/out/完成.xlsx", index=False)

progress_bar.update(10)
progress_bar.close()
